<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Transactions</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="C#, SQLite, ADO.NET, SqliteTransaction, transactions, rollback, commit">
<meta name="description" content="In this part of the SQLite C# tutorial, 
we work with database transactions.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">

<div>
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

</div>


<div class="content2">

<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Transactions</h1>

<p>
In this chapter, we will work with transactions. First, we provide some
basic definitions. Then we will have programs that show, how to work
with transactions. 
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
A <b>transaction</b> is an atomic unit of database operations 
against the data in one or more databases. The effects of all the SQL statements
in a transaction can be either all committed to the database or all rolled back.
</p>

<p>
In SQLite, any command other than the SELECT will start an implicit transaction. 
Also, within a transaction a command like CREATE TABLE ..., VACUUM, PRAGMA, will 
commit previous changes before executing.
</p>

<p>
Manual transactions are started with the BEGIN TRANSACTION statement and finished 
with the COMMIT OR ROLLBACK statements.
</p>

<p>
SQLite supports three non-standard transaction levels. DEFERRED, IMMEDIATE and EXCLUSIVE. 
SQLite automatically puts each command into its own transaction unless we start our
own transaction. Note that this may be influenced by the driver too. SQLite Python
driver has the autocommit mode turned off by default and the first SQL command
starts a new transaction.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        
        
        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using (SqliteCommand cmd = con.CreateCommand())
            {
                cmd.CommandText = "DROP TABLE IF EXISTS Friends";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                                    Name TEXT)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jim')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Robert')";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Julian')";
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
    }
}
</pre>

<p>
We create a Friends table and fill it with data. We do not explicitly start a 
transaction, nor we call commit or rollback methods. Yet the data is written 
to the table. It is because by default, we work in the autocommit mode. 
In this mode each SQL statement is immediately effective.
</p>

<pre class="explanation">
cmd.CommandText = "DROP TABLE IF EXISTS Friends";
cmd.ExecuteNonQuery();
cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                    Name TEXT)";
cmd.ExecuteNonQuery();
</pre>

<p>
We drop the Friends table if it already exists. Then we create the table with 
the CREATE TABLE statement.
</p>

<pre class="explanation">
cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
cmd.ExecuteNonQuery();
...
</pre>

<p>
We insert two rows. 
</p>


<pre>
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
</pre>

<p>
The Friends table was successfully created. 
</p>


<hr class="btm"> 

<p>
In the second example we will start a custom transaction with 
the <code>BeginTransaction()</code> method.
</p>


<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        
        
        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using(SqliteTransaction tr = con.BeginTransaction())
            {
                using (SqliteCommand cmd = con.CreateCommand())
                {

                    cmd.Transaction = tr;
                    cmd.CommandText = "DROP TABLE IF EXISTS Friends";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                                        Name TEXT)";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jim')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Robert')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Julian')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jane')";
                    cmd.ExecuteNonQuery();
                }

                tr.Commit();
            }

            con.Close();
        }
    }
}
</pre>

<p>
All SQL commands form a unit. Either all are saved or nothing is saved. This is
the basic idea behind transactions.
</p>

<pre class="explanation">
using(SqliteTransaction tr = con.BeginTransaction())
</pre>

<p>
The <code>BeginTransaction()</code> method starts a transaction.
</p>

<pre class="explanation">
cmd.Transaction = tr;
</pre>

<p>
We set the transaction within which the <code>SqliteCommand</code> executes.
</p>

<pre class="explanation">
tr.Commit();
</pre>

<p>
If everything ran OK, we commit the whole transaction to the database.
In case of an exception, the transaction is rolled back behind the scenes. 
</p>


<h2>Explicit rollback call</h2>

<p>
Now we are going to show an example, where we rollback manually a transaction, 
in case of an exception.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";   

        SqliteConnection con = null;     
        SqliteTransaction tr = null;
        SqliteCommand cmd = null;
        
        try 
        {
            con = new SqliteConnection(cs);
        
            con.Open();
    
            tr = con.BeginTransaction();
            cmd = con.CreateCommand();
                
            cmd.Transaction = tr;
            cmd.CommandText = "DROP TABLE IF EXISTS Friends";
            cmd.ExecuteNonQuery();
            cmd.CommandText = @"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, 
                                Name TEXT)";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Tom')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Rebecca')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jim')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Robert')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Julian')";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "INSERT INTO Friends(Name) VALUES ('Jane')";
            cmd.ExecuteNonQuery();
                
            tr.Commit();            

        } catch (SqliteException ex) 
        {
            Console.WriteLine("Error: {0}",  ex.ToString());

            if (tr != null)
            {
                try 
                {
                    tr.Rollback();
                    
                } catch (SqliteException ex2)
                {

                    Console.WriteLine("Transaction rollback failed.");
                    Console.WriteLine("Error: {0}",  ex2.ToString());

                } finally
                {
                    tr.Dispose();
                }
            }
        } finally 
        {
            if (cmd != null)
            {
                cmd.Dispose();
            }

            if (tr != null) 
            {
                tr.Dispose();
            }

            if (con != null)
            {
                try 
                {
                    con.Close();                    

                } catch (SqliteException ex)
                {

                    Console.WriteLine("Closing connection failed.");
                    Console.WriteLine("Error: {0}",  ex.ToString());

                } finally
                {
                    con.Dispose();
                }
            }
        }    
    }
}
</pre>

<p>
We create our own try, catch, finally blocks, where we deal with
possible issues.
</p>

<pre class="explanation">
} catch (SqliteException ex) 
{
    Console.WriteLine("Error: {0}",  ex.ToString());

    if (tr != null)
    {
        try 
        {
            tr.Rollback();
            
        } catch (SqliteException ex2)
        {

            Console.WriteLine("Transaction rollback failed.");
            Console.WriteLine("Error: {0}",  ex2.ToString());

        } finally
        {
            tr.Dispose();
        }
    }
}
</pre>

<p>
When an exception was thrown during the creation of the Friends table,
we call the <code>Rollback()</code> method. Even when doing rollback,
there might occur an exception. So we check this scenarion also.
</p>

<pre class="explanation">
if (cmd != null)
{
    cmd.Dispose();
}

if (tr != null) 
{
    tr.Dispose();
}
</pre>

<p>
When all goes OK, we dispose the resources. 
</p>

<pre class="explanation">
if (con != null)
{
    try 
    {
        con.Close();                    

    } catch (SqliteException ex)
    {

        Console.WriteLine("Closing connection failed.");
        Console.WriteLine("Error: {0}",  ex.ToString());

    } finally
    {
        con.Dispose();
    }
}
</pre>

<p>
When closing a connection, we might receive another exception. 
We handle this case here.
</p>


<h2>Errors</h2>

<p>
When there is an error in the transaction, the transaction is rolled back 
an no changes are committed to the database.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        
        
        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using(SqliteTransaction tr = con.BeginTransaction())
            {
                using (SqliteCommand cmd = con.CreateCommand())
                {

                    cmd.Transaction = tr;
                    cmd.CommandText = "UPDATE Friends SET Name='Thomas' WHERE Id=1";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
                    cmd.ExecuteNonQuery();
                }

                tr.Commit();
            }

            con.Close();
        }
    }
}
</pre>

<p>
In the code example we want to change two names. There are two statements 
which form a transaction. There is an error in the second SQL statement. 
Therefore the transaction is rolled back.
</p>

<pre class="explanation">
cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
</pre>

<p>
The name of the table is incorrect. There is no Friend table in 
the database.
</p>

<pre>
$ mono trans_error.exe 

Unhandled Exception: Mono.Data.Sqlite.SqliteException: SQLite error
no such table: Friend
...
</pre>

<p>
Running the example will display this error message. The transaction is rolled back.
</p>

<pre>
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim
4|Robert
5|Julian
6|Jane
</pre>

<p>
No changes took place in the Friends table. Even if the first UPDATE 
statement was correct.
</p>

<hr class="btm"> 

<p>
We will again try to change two rows; this time without using the
<code>SqliteTransaction</code>.
</p>

<pre class="code">
using System;
using Mono.Data.Sqlite;

public class Example
{

    static void Main() 
    {
        string cs = "URI=file:test.db";        
        
        using (SqliteConnection con = new SqliteConnection(cs)) 
        {
            con.Open();

            using (SqliteCommand cmd = con.CreateCommand())
            {

                cmd.CommandText = "UPDATE Friends SET Name='Thomas' WHERE Id=1";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
                cmd.ExecuteNonQuery();
            }

            con.Close();
        }
    }
}
</pre>

<p>
We try to update two names in the Friends table. Tom to Thomas and
Robert to Bob. 
</p>

<pre class="explanation">
cmd.CommandText = "UPDATE Friend SET Name='Bob' WHERE Id=4";
cmd.ExecuteNonQuery();
</pre>

<p>
This UPDATE statement is incorrect.
</p>


<pre>
$ mono notrans_error.exe 

Unhandled Exception: Mono.Data.Sqlite.SqliteException: SQLite error
no such table: Friend
</pre>

<p>
We receive the same error message as in the previous example.
</p>

<pre>
sqlite> SELECT * FROM Friends;
1|Thomas
2|Rebecca
3|Jim
4|Robert
5|Julian
6|Jane
</pre>

<p>
However this time, the first UPDATE statement was saved. The second one
was not. 
</p>

<p>
In this part of the SQLite C# tutorial, we have worked with transactions.
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 18, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

